package com.map.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.table.DefaultTableModel;

public class Database {
	private DefaultTableModel model;
	private Connection connection = null;
	private ResultSet result = null;
	private Statement statement = null;

	public void ConnectApplicationDB() throws ClassNotFoundException {
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection("jdbc:sqlite:" + ".//database//app.db");
			statement = connection.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void exceuteQueryStatement(String query) throws SQLException {
		statement.execute(query);

	}

	public DefaultTableModel readData(String tableName) {
		try {
			String mapId, mapName, mapType, costPrice, sellingPrice, quantity;
			result = statement.executeQuery("SELECT * FROM " + tableName);
			model = new DefaultTableModel(new String[][] {}, new String[] { "Map Id", "Map Name", "Map Type", "Cost Price", "Selling Price", "Quantity" });
			while (result.next()) {
				mapId = result.getString(1);
				mapName = result.getString(2);
				mapType = result.getString(3);
				costPrice = result.getString(4);
				sellingPrice = result.getString(5);
				quantity = result.getString(6);
				model.addRow(new String[] { mapId, mapName, mapType, costPrice, sellingPrice, quantity });
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return model;
	}

	public DefaultTableModel readDataOnCondition(String tableName, String whereClause) {
		try {
			String mapId, mapName, mapType, costPrice, sellingPrice, quantity;
			result = statement.executeQuery("SELECT * FROM " + tableName + " WHERE " + whereClause);
			model = new DefaultTableModel(new String[][] {}, new String[] { "Map Id", "Map Name", "Map Type", "Cost Price", "Selling Price", "Quantity" });
			while (result.next()) {
				mapId = result.getString(1);
				mapName = result.getString(2);
				mapType = result.getString(3);
				costPrice = result.getString(4);
				sellingPrice = result.getString(5);
				quantity = result.getString(6);
				model.addRow(new String[] { mapId, mapName, mapType, costPrice, sellingPrice, quantity });
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return model;
	}

	public void closeDB() {
		try {
			statement.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
